/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.sql.*;
import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author Aman, Mohamed Ghassen
 */
public class DAO {

    public ResultSet getAllFrom(String tableName) throws SQLException {
        Connection con = null;
        try {
            con = new ConnectionFactory().getConnection();
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Connection setup failed!!!");
        }
        String query = "select * from \"ADMIN\".\"" + tableName + "\"";
        PreparedStatement stmt = con.prepareStatement(query);
        ResultSet resultSet = stmt.executeQuery();
        return resultSet;
    }
    public ResultSet getSettingsOf(String username) throws SQLException {
        Connection con = null;
        try {
            con = new ConnectionFactory().getConnection();
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Connection setup failed!!!");
        }
        String query = "select * from \"ADMIN\".\"Users\" where \"Username\" = '"+username+"'";
        PreparedStatement stmt = con.prepareStatement(query);
        ResultSet resultSet = stmt.executeQuery();
        return resultSet;
    }
    
    public DefaultTableModel getModelOf(String table) throws SQLException {
        ResultSet rs = getAllFrom(table);
        ResultSetMetaData rsmd = rs.getMetaData();
        String[] columnNames = new String[rsmd.getColumnCount()];
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
            columnNames[i - 1] = rsmd.getColumnName(i);
        }
        DefaultTableModel newTable = new DefaultTableModel(new Object[][]{},
                columnNames);
        while (rs.next()) {
            String[] rowData = new String[rsmd.getColumnCount()];
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                rowData[i - 1] = rs.getString(i);
            }
            newTable.addRow(rowData);
        }
        return newTable;
    }

    public String getPasswordOf(String username) throws SQLException {
        Connection con = null;
        try {
            con = new ConnectionFactory().getConnection();
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Connection setup failed!!!");
        }
        String query = "select \"Password\" from \"ADMIN\".\"Users\" where \"Username\"='" + username + "'";
        PreparedStatement stmt = con.prepareStatement(query);
        ResultSet resultSet = stmt.executeQuery();
        resultSet.next();
        return resultSet.getString(1);
    }

    public boolean isAdmin(String username) throws SQLException {
        Connection con = null;
        try {
            con = new ConnectionFactory().getConnection();
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Connection setup failed!!!");
        }
        String query = "select \"User Type\" from \"ADMIN\".\"Users\" where \"Username\"='" + username + "'";
        PreparedStatement stmt = con.prepareStatement(query);
        ResultSet resultSet = stmt.executeQuery();
        resultSet.next();
        if (resultSet.getString(1).equals("a")) {
            return true;
        }
        return false;
    }

    public void updateTable(String table, int uid, String uname, String pass, int ac, int heat, int light, String utype) throws SQLException {
        Connection con = null;
        try {
            con = new ConnectionFactory().getConnection();
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Connection setup failed!!!");
        }
        String query = "update \"" + table + "\" set \"Username\" = '" + uname + "', \"Password\"= '" + pass
                + "', \"Air-Conditioning\"= " + ac + ", \"Heaters\"= " + heat + ", \"Lights\"= "
                + light + ", \"User Type\"= '" + utype + "' where \"UserID\" = " + uid + "";
        PreparedStatement stmt = con.prepareStatement(query);
        int row = stmt.executeUpdate();
        JOptionPane.showMessageDialog(null, "Database is Updated...");
    }

    public void updateTable(String table, int id, String level, int power) throws SQLException {
        Connection con = null;
        String query = null;
        try {
            con = new ConnectionFactory().getConnection();
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Connection setup failed!!!");
        }
        if (table.equals("AirConditioning") || table.equals("Heaters")) {
            query = "update \"" + table + "\" set \"Level\" = '" + level + "', \"Power\"= " + power
                    + " where \"ID\" = " + id + "";
        } else {
            query = "update \"" + table + "\" set \"Level\" = '" + level + "', \"Amount\"= " + power
                    + " where \"ID\" = " + id + "";
        }
        PreparedStatement stmt = con.prepareStatement(query);
        int row = stmt.executeUpdate();
        JOptionPane.showMessageDialog(null, "Database is Updated...");
    }

    public void deleteFrom(int id, String tableName) throws SQLException {
        Connection con = null;
        try {
            con = new ConnectionFactory().getConnection();
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Connection setup failed!!!");
        }
        String query = "delete from \"ADMIN\".\"" + tableName + "\" where \"ID\"=" + id;
        PreparedStatement stmt = con.prepareStatement(query);
        stmt.executeUpdate();
        JOptionPane.showMessageDialog(null, "Selected row is deleted...");
    }

    public void addData(String table, String uname, String pass, int ac, int heat, int light, String utype) throws SQLException {
        Connection con = null;
        try {
            con = new ConnectionFactory().getConnection();
        } catch (SQLException ex) {
            System.out.println("Connection setup failed!!!");
        }
        String query = "INSERT INTO \"" + table + "\" (\"Username\",\"Password\",\"Air-Conditioning\",\"Heaters\",\"Lights\",\"User Type\") VALUES ('"
                + uname + "','" + pass + "'," + ac + "," + heat + "," + light + ",'" + utype + "')";
        PreparedStatement stmt = con.prepareStatement(query);
        int row = stmt.executeUpdate();
        JOptionPane.showMessageDialog(null, "Data is added Successfully...");
    }

    public void addData(String table, String level, int power) throws SQLException {
        Connection con = null;
        String query = null;
        try {
            con = new ConnectionFactory().getConnection();
        } catch (SQLException ex) {
            System.out.println("Connection setup failed!!!");
        }
        if (table.equals("AirConditioning") || table.equals("Heaters")) {
            query = "insert into \"" + table + "\" (\"Level\", \"Power\") values ('" + level + "'," + power + ")";
        } else {
            query = "insert into \"" + table + "\" (\"Level\", \"Amount\") values ('" + level + "'," + power + ")";
        }
        PreparedStatement stmt = con.prepareStatement(query);
        int row = stmt.executeUpdate();
        JOptionPane.showMessageDialog(null, "Data is added Successfully...");
    }
    

}